﻿CREATE proc [dbo].[pro_Add_OrderCaseAnaly]

as
begin
declare @date varchar(50)
Set @date= convert(varchar(7),dateadd(month,-1,getdate()),120) --每个月第一天执行，统计上个月的数据
	If  (select count(Id) from OrderCaseAnaly where Times=@date)=0

		Insert into OrderCaseAnaly
		select p1.CompanyId as CompanyId,p1.TypeId as TypeId,p1.TypeName as TypeName,
		 CONVERT(varchar(7),p1.UpTime,120) as Times ,p1.ProductId as ProductId
		,sum(case  when c1.IndustryOneId=55 then 1 else 0 end) as jiaoyupeixun
		,sum(case  when c1.IndustryOneId=61 then 1 else 0 end) as nonglinmukuang
		,sum(case  when c1.IndustryOneId=65 then 1 else 0 end) as shehuifuwu
		,sum(case  when c1.IndustryOneId=66 then 1 else 0 end) as canyushipin
		,sum(case  when c1.IndustryOneId=68 then 1 else 0 end) as ITdianzi
		,sum(case  when c1.IndustryOneId=70 then 1 else 0 end) as yiliaojiankang
		,sum(case  when c1.IndustryOneId=288 then 1 else 0 end) as jituanzonghe
		,sum(case  when c1.IndustryOneId=294 then 1 else 0 end) as zhengfugongyi
		,sum(case  when c1.IndustryOneId=295 then 1 else 0 end) as qinggongzhizao
		,sum(case  when c1.IndustryOneId=296 then 1 else 0 end) as jiexiezhonggong
		,sum(case  when c1.IndustryOneId=297 then 1 else 0 end) as jienenghuanbao
		,sum(case  when c1.IndustryOneId=298 then 1 else 0 end) as jianzhuzhuangshi
		,sum(case  when c1.IndustryOneId=299 then 1 else 0 end) as wenhuachuban --into OrderCaseAnaly
		,GetDate()
		From Project p1 left join Customer c1 on p1.CusId=c1.CustomerId 
		Where  p1.IsHedge=0 and CONVERT(varchar(7),p1.UpTime,120)=@date   --'2014-12'
		Group By p1.CompanyId,CONVERT(varchar(7),p1.UpTime,120),p1.ProductId,p1.TypeId,p1.TypeName
	 Else
	   delete from OrderCaseAnaly where Times=@date

	   Insert into OrderCaseAnaly
		select p1.CompanyId as CompanyId,p1.TypeId as TypeId,p1.TypeName as TypeName,
		 CONVERT(varchar(7),p1.UpTime,120) as Times ,p1.ProductId as ProductId
		,sum(case  when c1.IndustryOneId=55 then 1 else 0 end) as jiaoyupeixun
		,sum(case  when c1.IndustryOneId=61 then 1 else 0 end) as nonglinmukuang
		,sum(case  when c1.IndustryOneId=65 then 1 else 0 end) as shehuifuwu
		,sum(case  when c1.IndustryOneId=66 then 1 else 0 end) as canyushipin
		,sum(case  when c1.IndustryOneId=68 then 1 else 0 end) as ITdianzi
		,sum(case  when c1.IndustryOneId=70 then 1 else 0 end) as yiliaojiankang
		,sum(case  when c1.IndustryOneId=288 then 1 else 0 end) as jituanzonghe
		,sum(case  when c1.IndustryOneId=294 then 1 else 0 end) as zhengfugongyi
		,sum(case  when c1.IndustryOneId=295 then 1 else 0 end) as qinggongzhizao
		,sum(case  when c1.IndustryOneId=296 then 1 else 0 end) as jiexiezhonggong
		,sum(case  when c1.IndustryOneId=297 then 1 else 0 end) as jienenghuanbao
		,sum(case  when c1.IndustryOneId=298 then 1 else 0 end) as jianzhuzhuangshi
		,sum(case  when c1.IndustryOneId=299 then 1 else 0 end) as wenhuachuban --into OrderCaseAnaly
		,GetDate()
		From Project p1 left join Customer c1 on p1.CusId=c1.CustomerId 
		Where  p1.IsHedge=0 and CONVERT(varchar(7),p1.UpTime,120)=@date   --'2014-12'
		Group By p1.CompanyId,CONVERT(varchar(7),p1.UpTime,120),p1.ProductId,p1.TypeId,p1.TypeName
end












--select 
--ci.Title as 行业,
--CONVERT(varchar(7),p1.CreateDate,120) as 月份,
-- Count(p1.Id) 数量,
-- p1.ProductCategoryId,
-- p1.ProductId
-- from Project p1 left join Customer c1 on p1.CusId=c1.CustomerId left join Customer_Industry ci on c1.IndustryOneId=ci.id 
--where ci.FId=0 and p1.IsHedge=0 and CONVERT(varchar(4),p1.CreateDate,120)='2015'
--group by ci.Title,CONVERT(varchar(7),p1.CreateDate,120),p1.ProductCategoryId, p1.ProductId


--select * from Customer_Industry where FId=0


--select Id from Product_Category where ParentId!=0

--select top(10) * from Project order by UpTime asc